package orm;


import java.util.Date;
import java.util.List;

import org.hibernate.*;

import exception.DBPersistorException;

public class ExchangeRateDAO extends ExchangeRate {

	private ExchangeDAO exchange;
/*	
	private static String dateToString(Date date) {

		Calendar cal = Calendar.getInstance();
		cal.setTime(date);
		
		return String.format("%04d-%02d-%02d", cal.get(Calendar.YEAR), cal.get(Calendar.MONTH)+1, cal.get(Calendar.DAY_OF_MONTH));
	}
*/

/*
    private static String getMaxAmplQuery(String dateFrom, String dateTo) {
    	return "SELECT code, (max-min) AS ampl FROM (" +
				   "SELECT cur1.currency_id, max(cur1.value), min(cur2.value) FROM currency_rate cur1 " +
				   "INNER JOIN currency_rate cur2 ON cur1.currency_id = cur2.currency_id " +
				   "WHERE cur1.date BETWEEN \'" + dateFrom + "\' and \'" + dateTo + "\' " +
				   "GROUP BY cur1.currency_id " +
				   "ORDER BY cur1.currency_id " +
				") AS foo, currency c WHERE currency_id = c.id ORDER BY ampl DESC LIMIT 1";
    }
*/

	@SuppressWarnings("unchecked")
	public static List<ExchangeRate> find(Date d) {

		List<ExchangeRate> retList = null;
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			Transaction tx = session.beginTransaction();
			Query q = session.createQuery("from ExchangeRate where date = :date");
			// Query q = session.createQuery("from ExchangeRate where cast(date as string) = :date");
			// q.setParameter("date", dateToString(d));
			q.setParameter("date", d);
			retList = q.list();
			tx.commit();
			
		} catch (HibernateException ex) {
			throw new DBPersistorException("Could not find persisted exchange rate object.");
		}
		
		return retList;
/*
		List<ExchangeRate> retList = new ArrayList<ExchangeRate>();
		String publicationDate = dateToString(date);

		try {
			//Connection connection = DBConnectionFactory.getInstance().getConnection();

			String q1 = "SELECT * FROM currency_rate WHERE date = \'" + publicationDate + "\'";
			Statement stat1 = connection.createStatement();
			ResultSet resultSet1 = stat1.executeQuery(q1);
			
			while (resultSet1.next()) {
	
				int currency_id = resultSet1.getInt("currency_id");
				BigDecimal value = new BigDecimal(resultSet1.getDouble("value"));
	
				String q2 = "SELECT code FROM currency WHERE id = " + currency_id;
				Statement stat2 = connection.createStatement();
				ResultSet resultSet2 = stat2.executeQuery(q2);
				resultSet2.next();
				String code = resultSet2.getString("code");
				
				ExchangeDAO cd = new ExchangeDAO(code);
				ExchangeRateDAO crd = new ExchangeRateDAO();
				crd.setExchange(cd);
				crd.setDate(date);
				crd.setValue(value);
				
				retList.add(crd);
	
			}
		} catch (PSQLException ex) {

		} catch (SQLException ex) {
			throw new DbConnectionException();
		}
		
		return retList;
*/
	}

	public static void persist(ExchangeRate cr) {

		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			Transaction tx = session.beginTransaction();
			session.save(cr);
			tx.commit();
		} catch (HibernateException e) {
			e.printStackTrace();
			throw new DBPersistorException("Could not persist exchange rate object.");
		}
/*
		try {
			// Connection connection = DBConnectionFactory.getInstance().getConnection();

			Integer currency_id = ExchangeDAO.persist(connection, cr.getExchange());

			String publicationDate = dateToString(cr.getDate());
			
			String q1 = "SELECT id FROM currency_rate WHERE currency_id = " + currency_id.toString() + " AND date = \'" + publicationDate + "\'";
			Statement stat1 = connection.createStatement();
			ResultSet resultSet1 = stat1.executeQuery(q1);

			if (!resultSet1.next()) {
				String query = "INSERT INTO currency_rate (currency_id, date, value) VALUES (" + currency_id + ", \'" + publicationDate + "\'" + ", \'" + cr.getValue() + "\')";
				PreparedStatement prepStatement = connection.prepareStatement(query);
				prepStatement.executeUpdate();
			}

			return null;
			
		} catch (SQLException e) {

			throw new DBPersistorException("Could not persist currency rate object.");
		}
*/
	}
	
	public static void persist(List<ExchangeRate> list) {

		for (ExchangeRate cr : list) {
			ExchangeRateDAO.persist(cr);
		}

	/*
		for (ExchangeRate cr : list) {
			ExchangeRateDAO.persist(connection, cr);
		}
		return null;
	*/
	}

	public static ExchangeRate findExchangeRate(Date date, String currCode) {
		
		ExchangeRate retObj = null;
		
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			Transaction tx = session.beginTransaction();
			Query q = session.createQuery("from ExchangeRate where exchange.code = :currCode and date = :date");
			q.setParameter("currCode", currCode);
			q.setParameter("date", date);		

			retObj = (ExchangeRate) q.uniqueResult();
			tx.commit();
			
		} catch (HibernateException ex) {
			throw new DBPersistorException("Could not find persisted exchange rate object.");
		}
		
		return retObj;
		
/*
		String query = "SELECT value FROM currency_rate, currency " +
				   		"WHERE currency_rate.currency_id = currency.id " + 
				   		"AND currency.code = \'" + currCode + "\' " +
				   		"AND currency_rate.date = \'" + dateToString(date) + "\'";
		
		try {
			// Connection connection = DBConnectionFactory.getInstance().getConnection();
			
			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			ExchangeRateDAO ret = null;
			
			if (rs.next()) {
				ExchangeDAO c = new ExchangeDAO(currCode);
				ret = new ExchangeRateDAO();
				ret.setExchange(c);
				ret.setDate(date);
				ret.setValue(new BigDecimal(rs.getDouble("value")));
			}
			
			return ret;
			
		} catch (SQLException e) {
			throw new DBPersistorException("Could not retrieve currency rate object.");
		}
*/
	}

    public static ExchangeRate findLowestExchangeRate(Date date) {

    	ExchangeRate retObj = null;
    	try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			Transaction tx = session.beginTransaction();
			String query = "from ExchangeRate where date = :date order by value asc";
			retObj = (ExchangeRate) session.createQuery(query).setParameter("date", date)
															  .setMaxResults(1)
															  .uniqueResult();
			tx.commit();
		} catch (HibernateException ex) {
			throw new DBPersistorException("Could not find persisted exchange rate object.");
		}
    	
    	return retObj;
/*
    	String query = "SELECT code, value FROM currency, currency_rate " +
    					"WHERE currency_rate.currency_id = currency.id " +
    					"AND currency_rate.date = \'" + dateToString(date) + "\' " +
    					"ORDER BY value ASC LIMIT 1";
    	
		try {
			// Connection connection = DBConnectionFactory.getInstance().getConnection();

			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			ExchangeRateDAO ret = null;
			
			if (rs.next()) {
				ExchangeDAO c = new ExchangeDAO(rs.getString("code"));
				ret = new ExchangeRateDAO();
				ret.setExchange(c);
				ret.setDate(date);
				ret.setValue(new BigDecimal(rs.getDouble("value")));
			}
			
			return ret;
			
		} catch (SQLException e) {
			throw new DBPersistorException("Could not retrieve currency rate object.");
		}
*/
    }

	public static ExchangeRate findHighestExchangeRate(Date date) {

		ExchangeRate retObj = null;
    	try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			Transaction tx = session.beginTransaction();
			String query = "from ExchangeRate where date = :date order by value desc";
			retObj = (ExchangeRate) session.createQuery(query).setParameter("date", date)
															  .setMaxResults(1)
															  .uniqueResult();
			tx.commit();
		} catch (HibernateException ex) {
			throw new DBPersistorException("Could not find persisted exchange rate objects.");
		}

    	return retObj;
/*
    	String query = "SELECT code, value FROM currency, currency_rate " +
    					"WHERE currency_rate.currency_id = currency.id " +
    					"AND currency_rate.date = \'" + dateToString(date) + "\' " +
    					"ORDER BY value DESC LIMIT 1";
    	
		try {
			// Connection connection = DBConnectionFactory.getInstance().getConnection();

			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			ExchangeRateDAO ret = null;
			
			if (rs.next()) {
				ExchangeDAO c = new ExchangeDAO(rs.getString("code"));
				ret = new ExchangeRateDAO();
				ret.setExchange(c);
				ret.setDate(date);
				ret.setValue(new BigDecimal(rs.getDouble("value")));
			}
			
			return ret;
			
		} catch (SQLException e) {
			throw new DBPersistorException("Could not retrieve currency rate object.");
		}
*/
	}

    public static ExchangeRate findLowestExchangeRate(Date from, Date to) {
    	
    	ExchangeRate retObj = null;
    	
    	try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			Transaction tx = session.beginTransaction();
			String query = "from ExchangeRate where date between :date1 and :date2 order by value asc";
			retObj = (ExchangeRate) session.createQuery(query).setParameter("date1", from)
															.setParameter("date2", to)
															.setMaxResults(1)
															.uniqueResult();
			tx.commit();
		} catch (HibernateException ex) {
			throw new DBPersistorException("Could not find persisted exchange rate objects.");
		}
    	
    	return retObj;
/*
    	String query = "SELECT code, value, date FROM currency, currency_rate " +
    					"WHERE currency_rate.currency_id = currency.id " +
    					"AND currency_rate.date BETWEEN \'" + dateToString(from) + "\' AND \'" + dateToString(to) + "\' " +
    					"ORDER BY value ASC LIMIT 1";
    	
		try {
			// Connection connection = DBConnectionFactory.getInstance().getConnection();

			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			ExchangeRateDAO ret = null;
			
			if (rs.next()) {
				ExchangeDAO c = new ExchangeDAO(rs.getString("code"));
				ret = new ExchangeRateDAO();
				ret.setExchange(c);
				ret.setDate(rs.getDate("date"));
				ret.setValue(new BigDecimal(rs.getDouble("value")));
			}
			
			return ret;
			
		} catch (SQLException e) {
			throw new DBPersistorException("Could not retrieve currency rate object.");
		}
*/
    }

	public static ExchangeRate findHighestExchangeRate(Date from, Date to) {
		
		ExchangeRate retObj = null;
    	try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			Transaction tx = session.beginTransaction();
			String query = "from ExchangeRate where date between :date1 and :date2 order by value desc";
			retObj = (ExchangeRate) session.createQuery(query).setParameter("date1", from)
															  .setParameter("date2", to)
															  .setMaxResults(1)
															  .uniqueResult();
			tx.commit();
		} catch (HibernateException ex) {
			throw new DBPersistorException("Could not find persisted exchange rate objects.");
		}
    	
    	return retObj;
/*
    	String query = "SELECT code, value, date FROM currency, currency_rate " +
						"WHERE currency_rate.currency_id = currency.id " +
						"AND currency_rate.date BETWEEN \'" + dateToString(from) + "\' AND \'" + dateToString(to) + "\' " +
						"ORDER BY value DESC LIMIT 1";

		try {
			// Connection connection = DBConnectionFactory.getInstance().getConnection();

			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			ExchangeRateDAO ret = null;
			
			if (rs.next()) {
				ExchangeDAO c = new ExchangeDAO(rs.getString("code"));
				ret = new ExchangeRateDAO();
				ret.setExchange(c);
				ret.setDate(rs.getDate("date"));
				ret.setValue(new BigDecimal(rs.getDouble("value")));
			}
			
			return ret;
			
		} catch (SQLException e) {
			throw new DBPersistorException("Could not retrieve currency rate object.");
		}
*/
	}

	public static ExchangeRate findLowestExchangeRate(Date from, Date to, String currCode) {

		ExchangeRate retObj = null;
		
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			Transaction tx = session.beginTransaction();
			String query = "from ExchangeRate where exchange.code = :currCode and date between :date1 and :date2 order by value asc";
			
			retObj = (ExchangeRate) session.createQuery(query).setParameter("currCode", currCode)
															  .setParameter("date1", from)
															  .setParameter("date2", to)
															  .setMaxResults(1)
															  .uniqueResult();
			tx.commit();
			
		} catch (HibernateException ex) {
			throw new DBPersistorException("Could not find persisted exchange rate object.");
		}
		
		return retObj;
/*
    	String query = "SELECT code, value, date FROM currency, currency_rate " +
						"WHERE currency_rate.currency_id = currency.id " +
						"AND currency.code = \'" + currCode + "\' " +
						"AND currency_rate.date BETWEEN \'" + dateToString(from) + "\' AND \'" + dateToString(to) + "\' " +
						"ORDER BY value ASC LIMIT 1";

		try {
			// Connection connection = DBConnectionFactory.getInstance().getConnection();

			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			ExchangeRateDAO ret = null;
			
			if (rs.next()) {
				ExchangeDAO c = new ExchangeDAO(currCode);
				ret = new ExchangeRateDAO();
				ret.setExchange(c);
				ret.setDate(rs.getDate("date"));
				ret.setValue(new BigDecimal(rs.getDouble("value")));
			}
			
			return ret;
			
		} catch (SQLException e) {
			throw new DBPersistorException("Could not retrieve currency rate object.");
		}
*/
	}

	public static ExchangeRate findHighestExchangeRate(Date from, Date to, String currCode) {

		ExchangeRate retObj = null;
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			Transaction tx = session.beginTransaction();
			String query = "from ExchangeRate where exchange.code = :currCode and date between :date1 and :date2 order by value desc";
			
			retObj = (ExchangeRate) session.createQuery(query).setParameter("currCode", currCode)
															  .setParameter("date1", from)
															  .setParameter("date2", to)
															  .setMaxResults(1)
															  .uniqueResult();
			tx.commit();
			
		} catch (HibernateException ex) {
			throw new DBPersistorException("Could not find persisted exchange rate object.");
		}
		
		return retObj;
/*
    	String query = "SELECT code, value, date FROM currency, currency_rate " +
						"WHERE currency_rate.currency_id = currency.id " +
						"AND currency.code = \'" + currCode + "\' " +
						"AND currency_rate.date BETWEEN \'" + dateToString(from) + "\' AND \'" + dateToString(to) + "\' " +
						"ORDER BY value DESC LIMIT 1";

		try {
			// Connection connection = DBConnectionFactory.getInstance().getConnection();

			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			ExchangeRateDAO ret = null;
			
			if (rs.next()) {
				ExchangeDAO c = new ExchangeDAO(rs.getString("code"));
				ret = new ExchangeRateDAO();
				ret.setExchange(c);
				ret.setDate(rs.getDate("date"));
				ret.setValue(new BigDecimal(rs.getDouble("value")));
			}
			
			return ret;
			
		} catch (SQLException e) {
			throw new DBPersistorException("Could not retrieve currency rate object.");
		}
*/
	}

/*
    public static ExchangeRateDiff findBiggestExchangeDiff(Connection connection, Date from, Date to) {
    	try {
			String dateFrom = dateToString(from);
			String dateTo = dateToString(to);
			
			// Connection connection = DBConnectionFactory.getInstance().getConnection();

			Statement statement1 = connection.createStatement();
			ResultSet resultSet1 = statement1.executeQuery(getMaxAmplQuery(
					dateFrom, dateTo));
			if (!resultSet1.next())
				return null;
			
			ExchangeDAO curr = new ExchangeDAO(resultSet1.getString("code"));

			ExchangeRateDiff ret = new ExchangeRateDiff();
			
			ret.setValue(new BigDecimal(resultSet1.getDouble("ampl")));
			ret.setCurrency(curr);
			
			return ret;
			
		} catch (Exception e) {
			throw new DBPersistorException("Could not retrieve currency rate object.");
		}
    }
*/
	
	public ExchangeDAO getExchange() {

		return this.exchange;
	}
	
	public void setExchange(ExchangeDAO curr) {
		this.exchange = curr;
	}
	
}
